﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;

using System.Data;
using ServiceStack.Common.Utils;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using System.ComponentModel.DataAnnotations;


namespace DataStorage
{
    public class SqlRepository
    {
        IDbConnection db; 

        public SqlRepository()
        {
            OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
            db = GetFileConnectionString().OpenDbConnection();
        }

        public void CreateDatabaseTables()
        {
            //using (IDbConnection db = GetFileConnectionString().OpenDbConnection())
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.CreateTable<User>();
                dbConn.CreateTable<Bookmark>();
                dbConn.CreateTable<Tag>();
                dbConn.CreateTable<Lst>();
                dbConn.CreateTable<TombstoneBookmarks>();
                dbConn.CreateTable<StorageSync>();
            }
        }

        public void DropDatabaseTables()
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.DropTable<User>();
                dbConn.DropTable<Bookmark>();
                dbConn.DropTable<Tag>();
                dbConn.DropTable<Lst>();
                dbConn.DropTable<TombstoneBookmarks>();
                dbConn.DropTable<StorageSync>();
            }
        }

        public List<Bookmark> GetNewBookmarksToSyncToDropbox()
        {
            List<Bookmark> list = new List<Bookmark>();

            //Get the last sync date

            //Get the new bookmarks that were created after the last sync date

            return list;
        }

        public static List<Bookmark> GetUpdatedBookmarksToSyncToDropbox()
        {
            List<Bookmark> list = new List<Bookmark>();

            //Get the last sync date

            //Get the updated bookmarks that were created after the last sync date

            return list;
        }

        public void InsertBookmarkBatch(List<Bookmark> batch)
        {
            SqlExpressionVisitor<Bookmark> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Bookmark>();

            //using (IDbConnection db = GetFileConnectionString().OpenDbConnection())
            using (IDbCommand dbConn = db.CreateCommand())
            {
                //dbConn.CreateTable<StorageSync>();
                //ev.Insert(o => new { o.IsOffline, o.IsAuthenticated });
                //dbConn.InsertOnly(new StorageSync() { IsOffline = false, IsAuthenticated = true }, ev);
                
                //dbConn.InsertAll(batch);
            }
        }

        public void InsertTagBatch(List<Tag> batch)
        {
            SqlExpressionVisitor<Tag> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Tag>();

            //using (IDbConnection db = GetFileConnectionString().OpenDbConnection())
            using (IDbCommand dbConn = db.CreateCommand())
            {
                //dbConn.CreateTable<StorageSync>();
                //ev.Insert(o => new { o.IsOffline, o.IsAuthenticated });
                //dbConn.InsertOnly(new StorageSync() { IsOffline = false, IsAuthenticated = true }, ev);

                //dbConn.InsertAll(batch);
            }
        }

        public bool IsOffline()
        {
            bool isOff = false;
            isOff = GetSync() != null ? GetSync().IsOffline : false; 
            return isOff;
        }

        public bool IsAuthenicated()
        {
            return GetSync() != null ? GetSync().IsAuthenticated : false;
        }

        public User GetUser()
        {
            SqlExpressionVisitor<User> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<User>();

            using (IDbCommand dbConn = db.CreateCommand())
            {
                ev.Where(rn => rn.Id == 1);

                User s = null; //= dbConn.GetById<StorageSync>(1);

                s = dbConn.Select<User>(ev).SingleOrDefault();
                return s;
            }
        }

        public StorageSync GetSync()
        {
            SqlExpressionVisitor<StorageSync> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<StorageSync>();

            using (IDbCommand dbConn = db.CreateCommand())
            {
                ev.Where(rn => rn.Id == 1);

                StorageSync s = null; //= dbConn.GetById<StorageSync>(1);

                s = dbConn.Select<StorageSync>(ev).SingleOrDefault();
                return s;
            }
        }

        public void SaveDefaultStorageSync()
        {
            SqlExpressionVisitor<StorageSync> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<StorageSync>();

            //using (IDbConnection db = GetFileConnectionString().OpenDbConnection())
            using (IDbCommand dbConn = db.CreateCommand())
            {
                //dbConn.CreateTable<StorageSync>();
                ev.Insert(o => new { o.IsOffline, o.IsAuthenticated });
                dbConn.InsertOnly(new StorageSync() { IsOffline = false, IsAuthenticated = true }, ev);
            }
        }

        public void SaveLastUpdatedOnlyStorageSync()
        {
            SqlExpressionVisitor<StorageSync> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<StorageSync>();

            using (IDbCommand dbConn = db.CreateCommand())
            {
                ev.Where(rn => rn.Id == 1).Update(rn => rn.LastSynced);
                dbConn.UpdateOnly(new StorageSync() { LastSynced = DateTime.Now }, ev);
            }
        }

        public void UpdateIsAuthenicatedSync(bool IsAuthenticated)
        {
            SqlExpressionVisitor<StorageSync> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<StorageSync>();

            using (IDbCommand dbConn = db.CreateCommand())
            {
                ev.Where(rn => rn.Id == 1).Update(rn => rn.IsAuthenticated);
                dbConn.UpdateOnly(new StorageSync() { IsAuthenticated = IsAuthenticated }, ev);
            }
        }

        public void UpdateBookmark(Bookmark bookmark)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Update<Bookmark>(bookmark);
            }
        }

        public Bookmark GetBookmark(Guid guid)
        {
            SqlExpressionVisitor<Bookmark> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Bookmark>();

            using (IDbCommand dbConn = db.CreateCommand())
            {
                ev.Where(rn => rn.Id == guid);

                Bookmark b = null; 

                b = dbConn.Select<Bookmark>(ev).SingleOrDefault();

                return b;
            }
        }

        public void InsertBookmark(Bookmark bookmark)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Insert<Bookmark>(bookmark);
            }
        }
        
        public void DeleteBookmark(Bookmark bookmark)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Delete(bookmark);
            }
        }

        public List<Bookmark> GetAllBookmarks()
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                return dbConn.Select<Bookmark>();
            }
        }

        public void InsertTag(Tag tag)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Insert<Tag>(tag);
            }
        }

        public void UpdateTag(Tag tag)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Update<Tag>(tag);
            }
        }

        public void DeleteTag(Tag tag)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Delete(tag);
            }
        }

        //public void DeleteObject<T>(T instance)
        //{
        //    using (IDbCommand dbConn = db.CreateCommand())
        //    {
        //        dbConn.(instance);
        //    }
        //}

        public List<Tag> GetAllTags()
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                return dbConn.Select<Tag>();
            }
        }

        public void InsertList(Lst list)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Insert<Lst>(list);
            }
        }

        public void UpdateList(Lst list)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Update<Tag>(list);
            }
        }

        public void DeleteList(Lst list)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Delete(list);
            }
        }

        public List<Lst> GetAllLists()
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                return dbConn.Select<Lst>();
            }
        }

        public void InsertUser(User user)
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.Insert<User>(user);
            }
        }

        public void DeleteUser()
        {
            using (IDbCommand dbConn = db.CreateCommand())
            {
                dbConn.DeleteAll<User>();
            }
        }

        private string GetFileConnectionString()
        {
            var connectionString = "~/db.sqlite".MapAbsolutePath();
            //if (File.Exists(connectionString))
            //    File.Delete(connectionString);

            return connectionString;
        }
    }
}
